﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Windows.Forms;
using System.Diagnostics;
using System.Collections;
using System.Data.OleDb;
using Microsoft.Office.Interop.Excel;
using Microsoft.Office.Tools.Excel;
using Microsoft.Office.Interop;
using System.Runtime.InteropServices;
using System.Threading;

namespace template_dll
{
    public partial class ExpToExcel
    {
        public bool SaveDGVToCSVfile(string filename, DataGridView table, bool openInExcel)
        {
            try
            {
                StreamWriter sw = new StreamWriter(filename, false, Encoding.Unicode);

                List<int> col_n = new List<int>();
                foreach (DataGridViewColumn col in table.Columns)
                    if (col.Visible)
                    {
                        if (col.Index < table.ColumnCount - 1) sw.Write(col.HeaderText + "\t");
                        else sw.Write(col.HeaderText);

                        col_n.Add(col.Index);
                    }
                sw.WriteLine();
                int x = table.RowCount;
                if (table.AllowUserToAddRows) x--;

                for (int i = 0; i < x; i++)
                {
                    for (int y = 0; y < col_n.Count; y++)
                        if (y < col_n.Count - 1)
                            sw.Write(table[col_n[y], i].Value.ToString() + "\t");
                        else sw.Write(table[col_n[y], i].Value.ToString());
                    sw.Write("\r\n");
                }
                sw.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                return false;
            }

            if (openInExcel)
            {
                try
                {
                    System.Diagnostics.Process newProc = System.Diagnostics.Process.Start("excel.exe", "\"" + filename + "\"");
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                }
            }
            return true;
        }

        public bool SaveDGVToCSVfile(string filename, System.Data.DataTable table, bool openInExcel)
        {
            try
            {
                StreamWriter sw = new StreamWriter(filename, false, Encoding.Unicode);

                List<int> col_n = new List<int>();
                foreach (DataColumn col in table.Columns)
                {
                    if (col.Ordinal < table.Columns.Count - 1) sw.Write(col.ColumnName + "\t");
                    else sw.Write(col.ColumnName);
                    col_n.Add(col.Ordinal);
                }
                sw.WriteLine();
                int x = table.Rows.Count;

                for (int i = 0; i < x; i++)
                {
                    for (int y = 0; y < col_n.Count; y++)
                        if (y < col_n.Count - 1)
                            sw.Write(table.Rows[i][col_n[y]] + "\t");
                        else sw.Write(table.Rows[i][col_n[y]]);
                    sw.Write("\r\n");
                }
                sw.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                return false;
            }

            if (openInExcel)
            {
                try
                {
                    System.Diagnostics.Process newProc = System.Diagnostics.Process.Start("excel.exe", "\"" + filename + "\"");
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                }
            }
            return true;
        }

        public bool SaveDGVToCSVfile(string filename, System.Data.DataTable[] table, bool openInExcel)
        {
            try
            {
                StreamWriter sw = new StreamWriter(filename, false, Encoding.Unicode);
                foreach (System.Data.DataTable tbl in table)
                {
                    List<int> col_n = new List<int>();
                    foreach (DataColumn col in tbl.Columns)
                    {
                        if (col.Ordinal < tbl.Columns.Count - 1) sw.Write(col.ColumnName + "\t");
                        else sw.Write(col.ColumnName);
                        col_n.Add(col.Ordinal);
                    }
                    sw.WriteLine();
                    int x = tbl.Rows.Count;

                    for (int i = 0; i < x; i++)
                    {
                        for (int y = 0; y < col_n.Count; y++)
                            if (y < col_n.Count - 1)
                                sw.Write(tbl.Rows[i][col_n[y]] + "\t");
                            else sw.Write(tbl.Rows[i][col_n[y]]);
                        sw.Write("\r\n");
                    }
                }
                sw.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                return false;
            }

            if (openInExcel)
            {
                try
                {
                    System.Diagnostics.Process newProc = System.Diagnostics.Process.Start("excel.exe", "\"" + filename + "\"");
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Stop);
                }
            }
            return true;
        }

        public Hashtable CheckExcellProcesses(Hashtable myHashtable)
        {
            System.Diagnostics.Process[] AllProcesses = System.Diagnostics.Process.GetProcessesByName("excel");
            int iCount = 0;
            foreach (System.Diagnostics.Process ExcelProcess in AllProcesses)
            {
                myHashtable.Add(ExcelProcess.Id, iCount);
                iCount = iCount + 1;
            }
            return myHashtable;
        }

        public void KillExcel(Hashtable myHashtable)
        {
            System.Diagnostics.Process[] AllProcesses = System.Diagnostics.Process.GetProcessesByName("excel");
            foreach (System.Diagnostics.Process ExcelProcess in AllProcesses)
            {
                if (myHashtable.ContainsKey(ExcelProcess.Id) == false)
                    ExcelProcess.Kill();
            }
            AllProcesses = null;
        }

        public void ImportExcel(System.Data.DataTable table)
        {
            //- Загрузить данные из Excel
            try
            {
                OpenFileDialog dialog = new OpenFileDialog();
                dialog.Filter = "MS Excel | *.xls";
                dialog.Filter = "MS Excel | *.xlsx";
                if (dialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                {
                    OleDbConnection connection = new OleDbConnection(string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES\";", dialog.FileName));
                    OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM [Лист1$]", connection);
                    adapter.Fill(table);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }

        public void ExportToExcel(DataGridView grid)
        {
            if (grid.RowCount == 0)
            {
                MessageBox.Show("Пустой список товаров");
                return;
            }
            Microsoft.Office.Interop.Excel.Application Exl = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook wb;

            XlReferenceStyle RefStyle = Exl.ReferenceStyle;

            String TemplatePath = System.Windows.Forms.Application.StartupPath + @"\Экспорт данных.xltx";
            wb = Exl.Workbooks.Add();
            //try
            //{
            //    wb = Exl.Workbooks.Add(TemplatePath); // !!! 
            //}
            //catch (System.Exception ex)
            //{
            //    throw new Exception("Не удалось загрузить шаблон для экспорта " + TemplatePath + "\n" + ex.Message);
            //}
            Microsoft.Office.Interop.Excel.Worksheet ws = wb.Worksheets.get_Item(1) as Microsoft.Office.Interop.Excel.Worksheet;
            for (int j = 0; j < grid.Columns.Count; ++j)
            {
                (ws.Cells[1, j + 1] as Range).Value2 = grid.Columns[j].HeaderText;
            }
            object[,] days_mass = ToArray((grid.DataSource as DataView).ToTable());
            ws.get_Range(ws.Cells[2, 1], ws.Cells[2 + grid.RowCount - 1, grid.ColumnCount]).Value2 = days_mass;
            for (int j = 0; j < grid.Columns.Count; ++j)
            {
                if (grid.Columns[j].HeaderText.IndexOf("%") > -1)
                    ws.get_Range(ws.Cells[2, j + 1], ws.Cells[2 + grid.RowCount - 1, j + 1]).NumberFormat = "##0,00%";

            }
            ws.Columns.EntireColumn.AutoFit();
            ws.get_Range(ws.Cells[1, 1], ws.Cells[grid.RowCount + 1, grid.ColumnCount]).Borders.Color = Microsoft.Office.Interop.Excel.XlRgbColor.rgbBlack;
            Exl.ReferenceStyle = RefStyle;
            Exl.Visible = true;
            ReleaseExcel(Exl as Object);
        }
        private void ReleaseExcel(object excel)
        {
            // Уничтожение объекта Excel.
            Marshal.ReleaseComObject(excel);
            // Вызываем сборщик мусора для немедленной очистки памяти
            GC.GetTotalMemory(true);
        }
        public void CreateExcelFile(System.Data.DataTable table)
        {
            //- Создать файл Excel
            Microsoft.Office.Interop.Excel.Application ObjExcel = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook ObjWorkBook;
            Microsoft.Office.Interop.Excel.Worksheet ObjWorkSheet;
            XlReferenceStyle RefStyle = ObjExcel.ReferenceStyle;
            //- Создаём книгу
            ObjWorkBook = ObjExcel.Workbooks.Add(System.Reflection.Missing.Value);
            //- Создаём таблицу
            ObjWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ObjWorkBook.Sheets[1];
            int j = 0;
            for (j=0; j < table.Columns.Count; j++)
            {
                (ObjWorkSheet.Cells[1, j + 1] as Range).Value2 = table.Columns[j].Caption;
                if (table.Columns[j].Caption == "Плановый период")
                {
                    (ObjWorkSheet.Cells[2, j + 1] as Range).Value2 = "проставить на форме";
                    (ObjWorkSheet.Cells[2, j + 1] as Range).Font.Color = XlRgbColor.rgbRed;
                }
            }
            Range ran = ObjWorkSheet.Range["A1", ObjWorkSheet.Cells[j+20, j]];
            ran.Borders.Weight = 2;
            ObjWorkSheet.Columns.EntireColumn.AutoFit();
            ObjExcel.Visible = true;
            ObjExcel.UserControl = true;
        }

        public static object[,] ToArray(System.Data.DataTable data)
        {
            var ret = Array.CreateInstance(typeof(object), data.Rows.Count, data.Columns.Count) as object[,];
            for (int i = 0; i < data.Rows.Count; i++)
                for (int j = 0; j < data.Columns.Count; j++)
                    ret[i, j] = data.Rows[i][j];
            return ret;
        }
    }
}

